﻿using DataAccess.Exceptions;
using Model;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Globalization;

namespace DataAccess
{
    public static class SubjectDataAccess
    {
        public static string ConnString = Conexion.DataSource;

        public static Subject newSubject(String nameSubject, String acronym, char status, int credits, String description)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO [Materia] (nombre, sigla, estado, creditos, descripcion) VALUES (@V_nombre, @V_sigla, @V_estado, @V_creditos, @V_descripcion) ";
                cmd.Parameters.AddWithValue("V_nombre", nameSubject);
                cmd.Parameters.AddWithValue("V_sigla", acronym);
                cmd.Parameters.AddWithValue("V_estado", status);
                cmd.Parameters.AddWithValue("V_creditos", credits);
                cmd.Parameters.AddWithValue("V_descripcion", description);
                connection.Open();

                cmd.ExecuteNonQuery();
                Subject newSubject = getSubjectByNameSubjectAndAcronym(nameSubject, acronym);
                return (newSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al crear una nueva materia", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Subject modifySubject(long code, String nameSubject, String acronym, char status, int credits, String description)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE [Materia] SET nombre=@V_nombre, sigla=@V_sigla, estado=@V_estado, creditos=@V_creditos, descripcion=@V_descripcion WHERE codMateria=@V_codMateria";
                cmd.Parameters.AddWithValue("V_nombre", nameSubject);
                cmd.Parameters.AddWithValue("V_sigla", acronym);
                cmd.Parameters.AddWithValue("V_estado", status);
                cmd.Parameters.AddWithValue("V_creditos", credits);
                cmd.Parameters.AddWithValue("V_descripcion", description);
                cmd.Parameters.AddWithValue("V_codMateria", code);
                connection.Open();

                cmd.ExecuteNonQuery();
                Subject newSubject = getSubjectByNameSubjectAndAcronym(nameSubject, acronym);
                return (newSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al crear una nueva materia", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }


        //Get one Subject
        public static Subject getSubjectByNameSubjectAndAcronym(String nameSubject, string acronym)
        {
            SqlConnection sqlConnection = null;
            Subject result = null;
            try
            {
                sqlConnection = new SqlConnection(ConnString);
                SqlCommand cmd = sqlConnection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Materia] Where nombre=@V_nameSubject AND sigla=@V_acronym";
                cmd.Parameters.AddWithValue("V_nameSubject", nameSubject);
                cmd.Parameters.AddWithValue("V_acronym", acronym);
                sqlConnection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = new Subject();
                    result.code = Convert.ToInt32(reader["codMateria"].ToString());
                    result.acronym = reader["sigla"].ToString();
                    result.nameSubject = reader["nombre"].ToString();
                    result.description = reader["descripcion"].ToString();
                    result.credits = Convert.ToInt32(reader["creditos"].ToString());
                    result.status = Convert.ToChar(reader["estado"].ToString());
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static char getStatusByNameSubjectAndAcronym(String nameSubject, string acronym)
        {
            SqlConnection sqlConnection = null;
            char result = '0';
            try
            {
                sqlConnection = new SqlConnection(ConnString);
                SqlCommand cmd = sqlConnection.CreateCommand();
                cmd.CommandText = "SELECT estado FROM [Materia] Where nombre=@V_nameSubject AND sigla=@V_acronym";
                cmd.Parameters.AddWithValue("V_nameSubject", nameSubject);
                cmd.Parameters.AddWithValue("V_acronym", acronym);
                sqlConnection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = Convert.ToChar(reader["estado"].ToString());
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }


        //Get Collection List
        public static Collection<Subject> getAll(bool onlyActive)
        {
            SqlConnection sqlConnection = null;
            Collection<Subject> listSubject = null;
            try
            {
                listSubject = new Collection<Subject>();
                sqlConnection = new SqlConnection(ConnString);
                SqlCommand cmd = sqlConnection.CreateCommand();
                if (onlyActive == false)
                {
                    cmd.CommandText = "SELECT * FROM [Materia]";
                }
                else
                {
                    cmd.CommandText = "SELECT * FROM [Materia] WHERE estado='A'";
                }

                sqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Subject result = new Subject();
                    result.code = Convert.ToInt32(reader["codMateria"].ToString());
                    result.acronym = reader["sigla"].ToString();
                    result.nameSubject = reader["nombre"].ToString();
                    result.description = reader["descripcion"].ToString();
                    result.credits = Convert.ToInt32(reader["creditos"].ToString());
                    result.status = Convert.ToChar(reader["estado"].ToString());

                    listSubject.Add(result);
                }
                return (listSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Subject> getSubjectByName(string nameSubject, bool onlyActive)
        {
            SqlConnection sqlConnection = null;
            Collection<Subject> listSubject = null;
            try
            {
                listSubject = new Collection<Subject>();
                sqlConnection = new SqlConnection(ConnString);
                SqlCommand cmd = sqlConnection.CreateCommand();
                if (onlyActive == false)
                {
                    cmd.CommandText = "SELECT * FROM [Materia] WHERE nombre LIKE @V_nameSubject";
                }
                else
                {
                    cmd.CommandText = "SELECT * FROM [Materia] WHERE nombre LIKE @V_nameSubject AND estado='A'";
                }
                cmd.Parameters.AddWithValue("V_nameSubject", nameSubject + "%");

                sqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Subject result = new Subject();
                    result.code = Convert.ToInt32(reader["codMateria"].ToString());
                    result.acronym = reader["sigla"].ToString();
                    result.nameSubject = reader["nombre"].ToString();
                    result.description = reader["descripcion"].ToString();
                    result.credits = Convert.ToInt32(reader["creditos"].ToString());
                    result.status = Convert.ToChar(reader["estado"].ToString());

                    listSubject.Add(result);
                }
                return (listSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Subject getSubjectByCode(string code, bool onlyActive)
        {
            SqlConnection sqlConnection = null;
            Subject result = null;
            try
            {
                sqlConnection = new SqlConnection(ConnString);
                SqlCommand cmd = sqlConnection.CreateCommand();
                if (onlyActive == false)
                {
                    cmd.CommandText = "SELECT * FROM [Materia] WHERE codMateria=@V_codigo";
                }
                else
                {
                    cmd.CommandText = "SELECT * FROM [Materia] WHERE codMateria=@V_codigo AND estado='A'";
                }
                cmd.Parameters.AddWithValue("V_codigo", code);

                sqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = new Subject();
                    result.code = Convert.ToInt32(reader["codMateria"].ToString());
                    result.acronym = reader["sigla"].ToString();
                    result.nameSubject = reader["nombre"].ToString();
                    result.description = reader["descripcion"].ToString();
                    result.credits = Convert.ToInt32(reader["creditos"].ToString());
                    result.status = Convert.ToChar(reader["estado"].ToString());
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Subject> getSubjectLikeByCode(string code, bool onlyActive)
        {
            SqlConnection sqlConnection = null;
            Collection<Subject> listSubject = null;
            try
            {
                listSubject = new Collection<Subject>();
                sqlConnection = new SqlConnection(ConnString);
                SqlCommand cmd = sqlConnection.CreateCommand();
                if (onlyActive == false)
                {
                    cmd.CommandText = "SELECT * FROM [Materia] WHERE codMateria LIKE @V_codigo";
                }
                else
                {
                    cmd.CommandText = "SELECT * FROM [Materia] WHERE codMateria LIKE @V_codigo AND estado='A'";
                }
                cmd.Parameters.AddWithValue("V_codigo", code + "%");

                sqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Subject result = new Subject();
                    result.code = Convert.ToInt32(reader["codMateria"].ToString());
                    result.acronym = reader["sigla"].ToString();
                    result.nameSubject = reader["nombre"].ToString();
                    result.description = reader["descripcion"].ToString();
                    result.credits = Convert.ToInt32(reader["creditos"].ToString());
                    result.status = Convert.ToChar(reader["estado"].ToString());

                    listSubject.Add(result);
                }
                return (listSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Subject> getSubjectByAcronym(string acronym, bool onlyActive)
        {
            SqlConnection sqlConnection = null;
            Collection<Subject> listSubject = null;
            try
            {
                listSubject = new Collection<Subject>();
                sqlConnection = new SqlConnection(ConnString);
                SqlCommand cmd = sqlConnection.CreateCommand();
                if (onlyActive == false)
                {
                    cmd.CommandText = "SELECT * FROM [Materia] WHERE sigla LIKE @V_acronym";
                }
                else
                {
                    cmd.CommandText = "SELECT * FROM [Materia] WHERE sigla LIKE @V_nacronym AND estado='A'";
                }
                cmd.Parameters.AddWithValue("V_acronym", acronym + "%");

                sqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Subject result = new Subject();
                    result.code = Convert.ToInt32(reader["codMateria"].ToString());
                    result.acronym = reader["sigla"].ToString();
                    result.nameSubject = reader["nombre"].ToString();
                    result.description = reader["descripcion"].ToString();
                    result.credits = Convert.ToInt32(reader["creditos"].ToString());
                    result.status = Convert.ToChar(reader["estado"].ToString());

                    listSubject.Add(result);
                }
                return (listSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Collection<Subject> getSubjectAvailableInMonth(string gestion)
        {
            SqlConnection sqlConnection = null;
            Collection<Subject> listSubject = null;
            try
            {
                Collection<Horary> listHorarySubject = DataAccess.HoraryDataAccess.getHoraryByGestion(gestion);
                listSubject = new Collection<Subject>();

                for (int i = 0; i < listHorarySubject.Count; i++)
                {
                    if (listHorarySubject[i].activity == 'A')
                    {
                        sqlConnection = new SqlConnection(ConnString);
                        SqlCommand cmd = sqlConnection.CreateCommand();
                        cmd.CommandText = "SELECT * FROM [Materia] WHERE codMateria=@V_codMateria";
                        cmd.Parameters.AddWithValue("V_codMateria", DataAccess.SubjectToTeacherDataAccess.getSubjectToTeacherByCode(listHorarySubject[i].codSubjectToTeacher).codSubject);

                        sqlConnection.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            Subject result = new Subject();
                            result.code = Convert.ToInt32(reader["codMateria"].ToString());
                            result.acronym = reader["sigla"].ToString();
                            result.nameSubject = reader["nombre"].ToString();
                            result.description = reader["descripcion"].ToString();
                            result.credits = Convert.ToInt32(reader["creditos"].ToString());
                            result.status = Convert.ToChar(reader["estado"].ToString());

                            listSubject.Add(result);
                        }
                    }
                }
                return (listSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        //me
        public static object ViewOfertedSubjects()
        {
            
                SqlConnection connection = null;
                DataView dataView = null;
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = null;
                try
                {
                    ds.Locale = CultureInfo.CurrentCulture;
                    connection = new SqlConnection(ConnString);
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT codMateria,sigla,nombre,creditos,descripcion FROM MATERIA";
                    connection.Open();
                    adapter = new SqlDataAdapter(command);
                    adapter.Fill(ds, "MATERIA");
                    if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                    dataView = ds.Tables["MATERIA"].DefaultView;
                    return dataView;

                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error filtrando materias", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    if (ds != null)
                    {
                        ds.Dispose();
                    }
                    if (adapter != null)
                    {
                        adapter.Dispose();
                    }
                    SqlConnection.ClearAllPools();
                }
        }
        public static Subject GetONESubjectByName(string name)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Materia Where nombre = @nombre";
                cmd.Parameters.AddWithValue("nombre", name);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                Subject result = new Subject();

                while (reader.Read())
                {
                    result.code = Convert.ToInt32(reader["codMateria"].ToString());
                    result.acronym = reader["sigla"].ToString();
                    result.nameSubject = reader["nombre"].ToString();
                    result.description = reader["descripcion"].ToString();
                    result.credits = Convert.ToInt32(reader["creditos"].ToString());
                    result.status = Convert.ToChar(reader["estado"].ToString());

                }
                return result;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting the subject", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
    }
}
